package com.carreirati.evaluation.domain;

import java.util.List;
import javax.persistence.TypedQuery;
import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;


@RooJavaBean
@RooJson
@RooJpaActiveRecord(identifierType = VwGetAverageResultsByQuestionsPK.class, versionField = "", table = "vw_get_average_results_by_questions", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
public class VwGetAverageResultsByQuestions {

    public static List<com.carreirati.evaluation.domain.VwGetAverageResultsByQuestions> findAllVwGetAverageResultsByQuestions(long appraisalId, long questionId) {
        TypedQuery<VwGetAverageResultsByQuestions> q = entityManager().createQuery("SELECT o FROM VwGetAverageResultsByQuestions o WHERE o.id.appraisalId = :appraisalId AND o.id.questionId = :questionId", VwGetAverageResultsByQuestions.class);
        q.setParameter("appraisalId", appraisalId);
        q.setParameter("questionId", questionId);
        return q.getResultList();
    }

    public static List<com.carreirati.evaluation.domain.VwGetAverageResultsByQuestions> findAllVwGetAverageResultsByAppraisal(long appraisalId, long knowledgeAreaId) {
        return entityManager().createNativeQuery("SELECT * FROM Vw_Get_Average_Results_By_Questions o WHERE appraisal_Id = '"+appraisalId+"'  AND  knowledge_area_id = '"+ knowledgeAreaId +"'", VwGetAverageResultsByQuestions.class).getResultList();
    }
    
    public static List<com.carreirati.evaluation.domain.VwGetAverageResultsByQuestions> findAllVwGetAverageResultsByEvaluated(long appraisalId, long knowledgeAreaId) { 
    	//implement a method to only show historical values until date of appraisal.
    	String sqlquery = "SELECT * FROM vw_Get_average_results_by_questions WHERE user_Id in (SELECT distinct user_id FROM vw_Get_average_results_by_questions WHERE appraisal_id = '"+ appraisalId +"')  AND knowledge_area_id = '"+ knowledgeAreaId +"'";
    	sqlquery += " AND appraisal_To <= (SELECT max(appraisal_to) FROM vw_Get_average_results_by_questions WHERE appraisal_id = '"+ appraisalId +"')";
    	return entityManager().createNativeQuery(sqlquery, VwGetAverageResultsByQuestions.class).getResultList();
    }
}
